25 Days of DAX Fridays! Challenge 2021
Photo Credit: Pikisuperstar @ Freepik
Link to GitHub repository, including data and final output.
About the challenge
Welcome to the 1st edition of the 25 Days of Dax Fridays challenge by Ruth Pozuelo Martinez (Microsoft MVP) at Curbal.
In this challenge, 25 questions match the 25-day countdown to Christmas. The questions help to explore insights about various aspects of the underlying company. Participants must answer using DAX only. The questions draw from the NorthWind dataset with some modifications.
About the dataset
Microsoft originally developed the Northwind database as a sample database, which was then utilised as the foundation for their lessons in other database products for many years. Sales information for a firm named "Northwind Traders," which imports and exports speciality delicacies from all over the world, is available in the Northwind database. With customers, orders, inventory, purchasing, suppliers, shipping, and staff, the Northwind database makes a great instructional schema for a small-business ERP.
The Northwind dataset includes sample data for the following:
Data modelDay 1 = COUNTROWS(
FILTER(
Products,
Products[Discontinued] = false() &&
Products[UnitPrice] < 20
)
)Rationale: The FILTER function filters the Products table where the Discontinued status is false (current products) and the Unit price is less than $20.
Day 2: Which product is the most expensive?
Day 2 = CALCULATE(
MAX(Products[ProductName]),
Products[UnitPrice] = MAX(Products[UnitPrice])
)Rationale: CALCULATE returns the Product name of products with the highest Unit price. The first row reference in the filter context iterates through the second row reference.
Day 3: What is the average unit price for our products?
Day 3 = AVERAGE(Products[UnitPrice])Rationale: The AVERAGE function returns the average value in the Unit price column.
Day 4: How many products are above the average price?
Day 4 = CALCULATE(
COUNT(Products[ProductID]),
Products[UnitPrice] > AVERAGE(Products[UnitPrice])
)Rationale: Similar to question 2, the first row reference act as an iteration over the second row reference. The COUNT expression returns the number of products that match the filter context.
Day 5: How many products cost between $15 and $25? (inclusive)
Day 5 = COUNTROWS(
FILTER(
Products,
Products[UnitPrice] >= 15 &&
Products[UnitPrice] <= 25
)
)Rationale: The FILTER function reduces the Products table to products with unit prices between 15 and 25.
Day 6: What is the average number of products (not quantity) per order?
Day 6 = DIVIDE(
COUNTROWS(ALL(Orders)),
DISTINCTCOUNT(Orders[OrderID])
)Rationale: The ALL function ensure the numerator in the DIVIDE function is not affected by any filter context. DISTINCTCOUNT returns the number of unique order IDs. A COUNT function would not work here as one order may have multiple rows (multiple products in one order).
Day 7: What is the order value in $ of open orders? (Not shipped yet)
Day 7 = SUMX(
FILTER(
Orders,
ISBLANK(Orders[ShippedDate])
),
Orders[Quantity]*Orders[UnitPrice]
)Rationale: The FILTER function narrows down the Orders table to contain not yet shipped orders (without a Shipped date). To avoid context transition, the full expression of Quantity * Unit price is used instead of a measure.
Day 8: How many orders are “single item”? (only one product ordered)
Day 8 = COUNTROWS(
FILTER(
SUMMARIZE(
Orders,
Orders[OrderID],
"Frequency",
COUNT(Orders[CustomerID])
),
[Frequency] = 1)
)Rationale: The Frequency column in the SUMMARIZE functions COUNT the number of items in an order. The FILTER function then removes any orders with the number of items not 1.
Day 9: Average sales per transaction (Order ID) for “Romero y Tomillo”?
Day 9 = AVERAGEX(
SUMMARIZE(
FILTER(
Orders,
RELATED(Customers[CompanyName]) = "Romero y Tomillo"
),
Orders[OrderID],
"Sales",
SUMX(Orders,Orders[Quantity]*Orders[UnitPrice])
),
[Sales]
)Rationale: The FILTER function returns a table of orders made by Romero y Tomillo. The SUMMARIZE function returns a two-column table, a column for the unique order IDs, and another for the total value of that order with SUMX. Finally, the AVERAGEX function goes through the summrized table and return the average of the “Sales” column.
Day 10: How many days since “North/South” last purchase?
Day 10 = FORMAT(
TODAY() - CALCULATE(
LASTDATE(Orders[OrderDate]),
Customers[CompanyName] = "North/South"),
"General Number"
)Rationale: The CALCULATE function returns the date of the last order (via LASTDATE) made by North/South (in the filter context). Depending on the day this question is completed, the final answer may vary based on TODAY().
Day 11: How many customers have ordered only once?
Day 11 = COUNTROWS(
FILTER(
SUMMARIZE(
Customers,
Customers[CustomerID],
"Count",
DISTINCTCOUNT(Orders[OrderID])
),
[Count]=1)
)Rationale: SUMMARIZE returns a table with each customer IDs and the respective number of orders made. DISTINCTCOUNT must be used as one order can have more than 1 row. FILTER removes customers with 2 orders or more.
Day 12: How many new customers (first purchase) in 2021?
Day 12 = COUNTROWS(
FILTER(
SUMMARIZE(
Orders,
Orders[CustomerID],
"FirstOrder",
YEAR(
FIRSTDATE(Orders[OrderDate])
)=2021
),
[FirstOrder] = TRUE()
)
)Rationale: The SUMMARIZE function provide a table of unique customer IDs and a true/false column check if the YEAR of the order date of the first order is 2021. FILTER removes order made in years other than 2021.
Day 13: How many lost customers (no purchase) in 2021?
Day 13 = COUNTROWS(
FILTER(
SUMMARIZE(
Orders,
Orders[CustomerID],
"LastOrder",
YEAR(
LASTDATE(Orders[OrderDate])
)=2020
),
[LastOrder] = TRUE()
)
)Rationale: Similar to question 12, but the group by column now checks if the last order was made in 2020, ie no order in 2021.
Day 14: How many customers never purchased Queso Cabrales?
Day 14 = DISTINCTCOUNT(Customers[CompanyName])-
COUNTROWS(
SUMMARIZE(
FILTER(
Orders,
Orders[ProductID]=11
),
Orders[CustomerID],
Orders[ProductID]
)
)Rationale: FILTER returns a list of orders that have Queso Cabrales, which is then SUMMARIZE(d) by customer IDs and order IDs. COUNTROWS then provides the number of customers who have purchased Queso Cabrales. This amount is subtracted from the number of unique customers.
Day 15: How many customers have purchased only Queso Cabrales in an order (per Order ID)?
Day 15 = COUNTROWS(
NATURALINNERJOIN(
FILTER(
SUMMARIZE(
Orders,
Orders[CustomerID],
Orders[OrderID],
"count",
COUNT(Products[ProductID])
),
[count]=1
),
FILTER(
Orders,
Orders[ProductID]=11)
)
)Rationale: The first FILTER function returns a table of customers who have orders with only 1 item (COUNT of Product ID in the order is 1). The second filter function returns a list of customers who have purchased Queso Cabrales (product ID = 11). The NATURALINNERJOIN function match the intersection between these two tables.
Day 16: How many products are out of stock?
Day 16 = COUNTROWS(
FILTER(
Products,
Products[UnitsInStock]=0
)
)Rationale: FILTER for products that has 0 Units in stock.
Day 17: How many products need to be restocked? (based on reorder level)
Day 17 = CALCULATE(
COUNT(Products[ProductID]),
Products[UnitsInStock] < Products[ReorderLevel]
)Rationale: COUNT the product IDs that have Units in stock less than the Reorder level.
Day 18: How many products on order do we need to restock?
Day 18 = CALCULATE(
COUNT(Products[ProductID]),
FILTER(
Products,
Products[UnitsOnOrder] > Products[UnitsInStock]
)
)Rationale: COUNT the number of product IDs that satisfy the condition of Units on order is greater than Units in stock.
Day 19: What is the stocked value of discontinued products?
Day 19 = CALCULATE(
SUMX(
Products,
Products[UnitPrice]*Products[UnitsInStock]
),
FILTER(
Products,
Products[Discontinued] = TRUE()
)
)Rationale: SUMX returns the average value of products that has been discontinued (Discontinued states is TRUE).
Day 20: Which vendor has the highest stock value?
Day 20 = CALCULATE(
MAX(Suppliers[CompanyName]),
TOPN(
1,
SUMMARIZE(
Suppliers,
Suppliers[CompanyName],
"value",
SUMX(
Products,
Products[UnitsInStock] * Products[UnitPrice]
)
),
[value],
DESC
)
)Rationale: The first step is to SUMMARIZE suppliers by the value of their stock products. TOPN returns the record of the supplier with the highest stock value. The MAX function grabs the name of the supplier.
Day 21: How many employees (%) are female?
Day 21 = DIVIDE(
COUNTROWS(
FILTER(
Employees,
Employees[Gender] = "Female"
)
),
COUNTROWS(
ALL(Employees)
)
)Rationale: Count the number of female employees and divide it by the total number of employees.
Day 22: How many employees are 60 years old or over?
Day 22 = COUNTROWS(
FILTER(
Employees,
(TODAY() - Employees[BirthDate]
)/365.25 >= 60
)
)Rationale: Count the number of employees with a birthday 60 years away from TODAY (365.25 days per year on average).
Day 23: Which employee had the highest sales in 2021?
Day 23 = CALCULATE(
MAX(Employees[Full Name]),
TOPN(
1,
SUMMARIZE(
Employees,
Employees[Full Name],
"Sales",
SUMX(
FILTER(
Orders,
YEAR(Orders[OrderDate]) = 2021
),
Orders[UnitPrice] * Orders[Quantity]
)
),
[Sales],
DESC
)
)Rationale: Similar to question 20, we first SUMMARIZE employees with their sales in the year 2021 and get the name with the highest sales via TOPN and MAX.
Day 24: How many employees sold over $100k in 2021?
Day 24 = COUNTROWS(
FILTER(
SUMMARIZE(
Employees,
Employees[Full Name],
"Sales",
SUMX(
FILTER(
Orders,
YEAR(Orders[OrderDate]) = 2021
),
Orders[UnitPrice] * Orders[Quantity]
)
),
[Sales] > 100000
)
)Rationale: Similar to question 23 but, instead of filtering for the highest sales value, we filter for all sales amounts greater than $100,000.
Day 25: How many employees got hired in 1994?
Day 25 = COUNTROWS(
FILTER(
Employees,
YEAR(Employees[HireDate])=1994
)
)Rationale: Count the number of employees with the YEAR of the Hire date is 1994.